package net.zjitc.dao.impl;

import net.zjitc.dao.AdminProductDao;
import net.zjitc.pojo.CategorySecond;
import net.zjitc.pojo.Evaluate;
import net.zjitc.pojo.Product;
import net.zjitc.pojo.User;
import net.zjitc.utils.PageBean;
import net.zjitc.utils.db.C3P0Utils;
import org.apache.commons.beanutils.BeanUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.MapHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

/**
 * Created by Administrator on 2017/11/22.
 */
public class AdminProductDaoImpl implements AdminProductDao{
    public PageBean<Product> findPage(int page, int pageSize) {
        QueryRunner runner=new QueryRunner(C3P0Utils.getDataSource());
        PageBean<Product> pageBean=new PageBean<Product>();
        pageBean.setPage(page);
        pageBean.setPageSize(pageSize);
        String sql="select count(*) from product";
        String listsql="select * from product order by pid limit ?,?";
        try {
            Number num= (Number) runner.query(sql,new ScalarHandler());
            int count=num.intValue();
            pageBean.setCount(count);

            List<Product> list= (List<Product>) runner.query(listsql,new BeanListHandler(Product.class),(page-1)*pageSize,pageSize);
            pageBean.setPageList(list);
            return pageBean;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    public int addProduct(Product product) {
        QueryRunner runner=new QueryRunner(C3P0Utils.getDataSource());
        String sql="insert into product(pname,market_price,shop_price,image,pdesc,is_hot,pdate,csid) values(?,?,?,?,?,?,?,?)";
        try {
            int count=runner.update(sql,product.getPname(),product.getMarket_price(),product.getShop_price(),product.getImage(),product.getPdesc(),product.getIs_hot(),product.getPdate(),product.getCategorySecond().getCsid());
            return count;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    public Product findByPid(String pid) {
        QueryRunner runner=new QueryRunner(C3P0Utils.getDataSource());
        String sql="select * from product where pid=?";
        try {
            //Map将字段名和对应的值封装成一个map集合
            /*适用于一个字段对应一个值，且有两个类时
            * csid  1
            * cname ***
            * cid   1
            * */
            Map<String,Object> map=runner.query(sql,new MapHandler(),pid);
            //创建两个实例
            Product product=new Product();
            CategorySecond categorySecond=new CategorySecond();
            //将相应的内容放进去
            BeanUtils.populate(product,map);
            BeanUtils.populate(categorySecond,map);
            product.setCategorySecond(categorySecond);
            return product;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

    public int editProduct(Product product) {
        QueryRunner runner=new QueryRunner(C3P0Utils.getDataSource());
        String sql="update product set pname=?,market_price=?,shop_price=?,image=?,pdesc=?,is_hot=?,pdate=?,csid=? where pid=?";
        try {
            int count=runner.update(sql,product.getPname(),product.getMarket_price(),product.getShop_price(),product.getImage(),product.getPdesc(),product.getIs_hot(),product.getPdate(),product.getCategorySecond().getCsid(),product.getPid());
            return count;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    public int deleteProduct(String pid) {
        QueryRunner runner=new QueryRunner(C3P0Utils.getDataSource());
        String sql="delete from product where pid=?";
        try {
            int count=runner.update(sql,pid);
            return count;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    public List<Product> findHot() {
        QueryRunner runner=new QueryRunner(C3P0Utils.getDataSource());
        String sql="select * from product where is_hot=1";
        try {
            List<Product> list= (List<Product>) runner.query(sql,new BeanListHandler(Product.class));
            return list;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    public List<Product> findNew() {
        QueryRunner runner=new QueryRunner(C3P0Utils.getDataSource());
        String sql="select * from product order by pid desc limit 10";
        try {
            List<Product> list= (List<Product>) runner.query(sql,new BeanListHandler(Product.class));
            return list;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    public PageBean<Product> findPageByCid(int page, int pageSize, int cid) {
        PageBean<Product> pageBean=new PageBean<Product>();
        pageBean.setPage(page);
        pageBean.setPageSize(pageSize);

        QueryRunner runner=new QueryRunner(C3P0Utils.getDataSource());
        //三表关联
        String sqlCount="SELECT COUNT(*) FROM product p JOIN categorysecond cs,category c WHERE p.csid = cs.csid AND cs.cid = c.cid AND c.cid=? ";

        String sql="select p.* from product p JOIN categorysecond cs,category c where p.csid = cs.csid and cs.cid = c.cid and c.cid=? limit ?,?";
        try {
            //查询数据总数
            Number number= (Number) runner.query(sqlCount,new ScalarHandler(),cid);
            pageBean.setCount(number.intValue());

            //查询数据列表
            List<Product> list= (List<Product>) runner.query(sql,new BeanListHandler(Product.class),cid,(page-1)*pageSize,pageSize);
            pageBean.setPageList(list);

            return pageBean;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    public PageBean<Product> findPageByCsid(int page, int pageSize, int csid) {
        PageBean<Product> pageBean=new PageBean<Product>();
        pageBean.setPage(page);
        pageBean.setPageSize(pageSize);

        QueryRunner runner=new QueryRunner(C3P0Utils.getDataSource());
        //两表关联
        String sqlCount="SELECT COUNT(*) FROM product p JOIN categorysecond cs on p.csid = cs.csid  AND cs.csid=? ";

        String sql="select p.* from product p JOIN categorysecond cs on p.csid = cs.csid  and cs.csid=? limit ?,?";
        try {
            //查询数据总数
            Number number= (Number) runner.query(sqlCount,new ScalarHandler(),csid);
            pageBean.setCount(number.intValue());

            //查询数据列表
            List<Product> list= (List<Product>) runner.query(sql,new BeanListHandler(Product.class),csid,(page-1)*pageSize,pageSize);
            pageBean.setPageList(list);

            return pageBean;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    public List<Evaluate> findEvaluate(String pid){
        QueryRunner runner=new QueryRunner(C3P0Utils.getDataSource());
        String sql="SELECT * FROM evaluate e JOIN product p,USER u WHERE e.pid=p.pid AND e.uid=u.uid AND p.pid=?";
        //存入评价对象的集合
        List<Evaluate> evaluateList=new ArrayList<Evaluate>();
        //存入map对象的List集合
        List<Map<String,Object>> list=null;
        try {
            list=runner.query(sql,new MapListHandler(),pid);
            //每次从list中取出map对象，然后封装成三个实体类对象
            for (Map<String,Object> m:list){
                User user=new User();
                Product product=new Product();
                Evaluate evaluate=new Evaluate();
                //使用BeanUtils封装对象
                BeanUtils.populate(user,m);
                BeanUtils.populate(product,m);
                BeanUtils.populate(evaluate,m);
                //设置evaluate
                evaluate.setUser(user);
                evaluate.setProduct(product);
                //每个评价对象放入评价集合中去
                evaluateList.add(evaluate);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return evaluateList;
    }

    public int addEvaluate(Evaluate evaluate) {
        QueryRunner runner=new QueryRunner(C3P0Utils.getDataSource());
        String sql="insert into evaluate(content,uid,pid,title,date) values(?,?,?,?,?)";
        try {
            int count=runner.update(sql,evaluate.getContent(),evaluate.getUser().getUid(),evaluate.getProduct().getPid(),evaluate.getTitle(),evaluate.getDate());
            return count;
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return 0;
    }

}


